package com.zl_huang.example.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.zl_huang.example.model.Subject;
@Repository
public class SubjectDaoImpl implements ISubjectDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
//=============新增科目方法============================
	@Override
	public Integer createSubject(Subject subject) throws Exception {		
			String sql = "insert into exam_subject(name,description,test_count,total_time,total_score,status)"+
		 " values(?,?,?,?,?,0)";
	return jdbcTemplate.update(sql, new Object[]{subject.getName(),subject.getDescription(),
			                   subject.getTest_count(),subject.getTotal_time(),subject.getTotal_score()});
			
		
	}
//====================修改科目方法=========================
	@Override
	public Integer editSubject(Subject subject) throws Exception {
			String sql = "update exam_subject set name = ?,description = ?,test_count = ?,total_time = ?,total_score = ?,status = ? where sub_id ="+subject.getSub_id();
			return jdbcTemplate.update(sql, new Object[]{subject.getName(),subject.getDescription(),
	                   subject.getTest_count(),subject.getTotal_time(),subject.getTotal_score(),subject.getStatus()});
		
	}
//=================删除学员方法==============================
	@Override
	public Integer deleteSubject(int sub_id) throws Exception {
			String sql = "delete from exam_subject where sub_id = ?";
			return jdbcTemplate.update(sql, new Object[]{sub_id});	
	}
//==================查询所有学员方法==============================	
	@Override
	public List<Subject> findAllSubject() throws Exception {
		
		
			String sql = "select sub_id,name,description,test_count,total_time,total_score,status from exam_subject";
			List<Subject> subjectList = jdbcTemplate.query(sql,new RowMapper<Subject>(){
				public Subject mapRow(ResultSet rs,int num)throws SQLException{
					Subject subject = new Subject();
					subject.setSub_id(rs.getInt("sub_id"));
					subject.setName(rs.getString("name"));
					subject.setDescription(rs.getString("description"));
					subject.setTest_count(rs.getInt("test_count"));
					subject.setTotal_time(rs.getInt("total_time"));
					subject.setTotal_score(rs.getDouble("total_score"));
					subject.setStatus(rs.getInt("status"));
					return subject;
				}
			});
			return subjectList;
		
	}
//===========================查询某一科目方法	===========================
	@Override
	public Subject getSubjectById(int sub_id) throws Exception {
			String sql = "select sub_id,name,description,test_count,total_time,total_score,status from exam_subject where sub_id = ?";
			return jdbcTemplate.queryForObject(sql, new Object[]{sub_id}, new RowMapper<Subject>(){
				public Subject mapRow(ResultSet rs,int num)throws SQLException{
					Subject subject = new Subject();
					subject.setSub_id(rs.getInt("sub_id"));
					subject.setName(rs.getString("name"));
					subject.setDescription(rs.getString("description"));
					subject.setTest_count(rs.getInt("test_count"));
					subject.setTotal_time(rs.getInt("total_time"));
					subject.setTotal_score(rs.getDouble("total_score"));
					subject.setStatus(rs.getInt("status"));
					return subject;
				}
			});
		}
	

}
